package com.dianshang.dao;

import com.dianshang.pojo.order.CategoryReport;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;

public interface CategoryReportDao extends Mapper<CategoryReport> {
    // 昨天的数据统计(商品类目)
    // SELECT category_id1,category_id2,category_id3,DATE_FORMAT(o.pay_time, "%Y-%m-%d") birth,SUM(oi.num) num,SUM(oi.pay_money) money
    // FROM tb_order_item oi,tb_order o
    // WHERE oi.order_id = o.id AND o.pay_status = "1" AND DATE_FORMAT(o.pay_time, "%Y-%m-%d") = "2019-04-15"
    // GROUP BY category_id1,category_id2,category_id3,DATE_FORMAT(o.pay_time, "%Y-%m-%d")
    @Select("SELECT category_id1,category_id2,category_id3,DATE_FORMAT(o.pay_time, \"%Y-%m-%d\") birth,SUM(oi.num) num,SUM(oi.pay_money) money\n" +
            "     FROM tb_order_item oi,tb_order o\n" +
            "     WHERE oi.order_id = o.id AND o.pay_status = \"1\" AND DATE_FORMAT(o.pay_time, \"%Y-%m-%d\") = \"2019-04-15\"\n" +
            "     GROUP BY category_id1,category_id2,category_id3,DATE_FORMAT(o.pay_time, \"%Y-%m-%d\")")
    List<CategoryReport> getCategory(@Param("localDate") LocalDate localDate);

    /**
     * 创建视图(lemeng_goods.tb_category分类表)
     */
    // CREATE VIEW v_category AS SELECT id,name FROM lemeng_goods.tb_category WHERE parent_id = 0

    // 按日期统计一级分类数据总和
    // SELECT category_id1 categoryId1,c.`name` categoryName,SUM(num) num,SUM(money) money
    // FROM `tb_category_report`,v_category c
    // WHERE tb_category_report.category_id1 = c.id AND count_date >= "2012-11-11" AND count_date <= "2022-11-11"
    // GROUP BY categoryId1,c.`name`
    @Select("SELECT category_id1 categoryId1,c.`name` categoryName,SUM(num) num,SUM(money) money\n" +
            "FROM `tb_category_report`,v_category c\n" +
            "WHERE tb_category_report.category_id1 = c.id AND count_date >= #{date1} AND count_date <= #{date2}\n" +
            "GROUP BY categoryId1,c.`name`")
    List<Map> categoryCount(@Param("date1") String date1, @Param("date2") String date2);
}